﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Gift.DBUtility;//Please add references
namespace Gift.DAL
{
    /// <summary>
    /// 数据访问类:DALGoodsEntitys
    /// </summary>
    public partial class DALGoodsEntitys
    {
        public DALGoodsEntitys()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbGoodsEntitys");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Gift.Model.tbGoodsEntitys model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbGoodsEntitys(");
            strSql.Append("goods,barCode,goodsArtno,goodsWeight,weightUnit,marketPrice,salePrice,costPrice,integral,giveIntegral,inventory)");
            strSql.Append(" values (");
            strSql.Append("@goods,@barCode,@goodsArtno,@goodsWeight,@weightUnit,@marketPrice,@salePrice,@costPrice,@integral,@giveIntegral,@inventory)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@goods", SqlDbType.Int,4),
					new SqlParameter("@barCode", SqlDbType.NVarChar,50),
					new SqlParameter("@goodsArtno", SqlDbType.NVarChar,50),
					new SqlParameter("@goodsWeight", SqlDbType.Decimal,9),
					new SqlParameter("@weightUnit", SqlDbType.NVarChar,20),
					new SqlParameter("@marketPrice", SqlDbType.Decimal,9),
					new SqlParameter("@salePrice", SqlDbType.Decimal,9),
					new SqlParameter("@costPrice", SqlDbType.Decimal,9),
					new SqlParameter("@integral", SqlDbType.Int,4),
					new SqlParameter("@giveIntegral", SqlDbType.Decimal,9),
					new SqlParameter("@inventory", SqlDbType.Int,4)};
            parameters[0].Value = model.goods;
            parameters[1].Value = model.barCode;
            parameters[2].Value = model.goodsArtno;
            parameters[3].Value = model.goodsWeight;
            parameters[4].Value = model.weightUnit;
            parameters[5].Value = model.marketPrice;
            parameters[6].Value = model.salePrice;
            parameters[7].Value = model.costPrice;
            parameters[8].Value = model.integral;
            parameters[9].Value = model.giveIntegral;
            parameters[10].Value = model.inventory;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Gift.Model.tbGoodsEntitys model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoodsEntitys set ");
            strSql.Append("goods=@goods,");
            strSql.Append("barCode=@barCode,");
            strSql.Append("goodsArtno=@goodsArtno,");
            strSql.Append("goodsWeight=@goodsWeight,");
            strSql.Append("weightUnit=@weightUnit,");
            strSql.Append("marketPrice=@marketPrice,");
            strSql.Append("salePrice=@salePrice,");
            strSql.Append("costPrice=@costPrice,");
            strSql.Append("integral=@integral,");
            strSql.Append("giveIntegral=@giveIntegral,");
            strSql.Append("inventory=@inventory");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@goods", SqlDbType.Int,4),
					new SqlParameter("@barCode", SqlDbType.NVarChar,50),
					new SqlParameter("@goodsArtno", SqlDbType.NVarChar,50),
					new SqlParameter("@goodsWeight", SqlDbType.Decimal,9),
					new SqlParameter("@weightUnit", SqlDbType.NVarChar,20),
					new SqlParameter("@marketPrice", SqlDbType.Decimal,9),
					new SqlParameter("@salePrice", SqlDbType.Decimal,9),
					new SqlParameter("@costPrice", SqlDbType.Decimal,9),
					new SqlParameter("@integral", SqlDbType.Int,4),
					new SqlParameter("@giveIntegral", SqlDbType.Int,4),
					new SqlParameter("@inventory", SqlDbType.Int,4),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.goods;
            parameters[1].Value = model.barCode;
            parameters[2].Value = model.goodsArtno;
            parameters[3].Value = model.goodsWeight;
            parameters[4].Value = model.weightUnit;
            parameters[5].Value = model.marketPrice;
            parameters[6].Value = model.salePrice;
            parameters[7].Value = model.costPrice;
            parameters[8].Value = model.integral;
            parameters[9].Value = model.giveIntegral;
            parameters[10].Value = model.inventory;
            parameters[11].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbGoodsEntitys ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbGoodsEntitys ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.tbGoodsEntitys GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,goods,barCode,goodsArtno,goodsWeight,weightUnit,marketPrice,salePrice,costPrice,integral,giveIntegral,inventory from tbGoodsEntitys ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            Gift.Model.tbGoodsEntitys model = new Gift.Model.tbGoodsEntitys();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goods"] != null && ds.Tables[0].Rows[0]["goods"].ToString() != "")
                {
                    model.goods = int.Parse(ds.Tables[0].Rows[0]["goods"].ToString());
                }
                if (ds.Tables[0].Rows[0]["barCode"] != null && ds.Tables[0].Rows[0]["barCode"].ToString() != "")
                {
                    model.barCode = ds.Tables[0].Rows[0]["barCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsArtno"] != null && ds.Tables[0].Rows[0]["goodsArtno"].ToString() != "")
                {
                    model.goodsArtno = ds.Tables[0].Rows[0]["goodsArtno"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsWeight"] != null && ds.Tables[0].Rows[0]["goodsWeight"].ToString() != "")
                {
                    model.goodsWeight = decimal.Parse(ds.Tables[0].Rows[0]["goodsWeight"].ToString());
                }
                if (ds.Tables[0].Rows[0]["weightUnit"] != null && ds.Tables[0].Rows[0]["weightUnit"].ToString() != "")
                {
                    model.weightUnit = ds.Tables[0].Rows[0]["weightUnit"].ToString();
                }
                if (ds.Tables[0].Rows[0]["marketPrice"] != null && ds.Tables[0].Rows[0]["marketPrice"].ToString() != "")
                {
                    model.marketPrice = decimal.Parse(ds.Tables[0].Rows[0]["marketPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["salePrice"] != null && ds.Tables[0].Rows[0]["salePrice"].ToString() != "")
                {
                    model.salePrice = decimal.Parse(ds.Tables[0].Rows[0]["salePrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["costPrice"] != null && ds.Tables[0].Rows[0]["costPrice"].ToString() != "")
                {
                    model.costPrice = decimal.Parse(ds.Tables[0].Rows[0]["costPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["integral"] != null && ds.Tables[0].Rows[0]["integral"].ToString() != "")
                {
                    model.integral = int.Parse(ds.Tables[0].Rows[0]["integral"].ToString());
                }
                if (ds.Tables[0].Rows[0]["giveIntegral"] != null && ds.Tables[0].Rows[0]["giveIntegral"].ToString() != "")
                {
                    model.giveIntegral = int.Parse(ds.Tables[0].Rows[0]["giveIntegral"].ToString());
                }
                if (ds.Tables[0].Rows[0]["inventory"] != null && ds.Tables[0].Rows[0]["inventory"].ToString() != "")
                {
                    model.inventory = int.Parse(ds.Tables[0].Rows[0]["inventory"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,goods,barCode,goodsArtno,goodsWeight,weightUnit,marketPrice,salePrice,costPrice,integral,giveIntegral,inventory ");
            strSql.Append(" FROM tbGoodsEntitys ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,goods,barCode,goodsArtno,goodsWeight,weightUnit,marketPrice,salePrice,costPrice,integral,giveIntegral,inventory ");
            strSql.Append(" FROM tbGoodsEntitys ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbGoodsEntitys ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from tbGoodsEntitys T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataTable GetAllGoodsEntitys()
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select * from tbgoodsEntitys", new object[0]);
                table = DbHelperSQL.Query(commandText).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsEntitysByGeId(int geId)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,barCode,goodsArtno,goodsName,thumbImg,imgAlt,integral from view_googsEntitys where geid=@geId", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@geId", geId) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsEntitysByGoods(int goods)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,goods,barCode,goodsArtno,goodsWeight,weightUnit,marketPrice,salePrice,costPrice,integral,giveIntegral,inventory from tbgoodsEntitys where goods=@goods", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@goods", goods) };
                 table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsEntitysById(int id)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,goods,barCode,goodsArtno,goodsWeight,weightUnit,marketPrice,salePrice,costPrice,integral,giveIntegral,inventory from tbgoodsEntitys where id=@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@id", id) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public bool IncreaseGoodsEntitysInventory(int id, int inventory)
        {
            bool flag = false;
            try
            {
                string commandText = string.Format("update tbgoodsEntitys set inventory=(inventory+@inventory) where id=@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@id", id), new SqlParameter("@inventory", inventory) };
                if (Convert.ToInt32( DbHelperSQL.ExecuteSql(commandText, commandParameters)) > 0)
                {
                    flag = true;
                }
            }
            catch
            {
            }
            return flag;
        }



        public bool ModifyGoodsEntitysInventory(int id, int inventory)
        {
            bool flag = false;
            try
            {
                string commandText = string.Format("update tbgoodsEntitys set inventory=(inventory-@inventory) where id=@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@id", id), new SqlParameter("@inventory", inventory) };
                if (DbHelperSQL.ExecuteSql(commandText, commandParameters) > 0)
                {
                    flag = true;
                }
            }
            catch
            {
            }
            return flag;
        }

        public bool CheckBarCode(string barCode, int geId)
        {
            bool flag = false;
            try
            {
                string commandText = string.Format("select count(*) from tbGoodsEntitys where barCode=@barCode and id<>@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@barCode", barCode), new SqlParameter("@id", geId) };
                if (DbHelperSQL.ExecuteSql( commandText, commandParameters) > 0)
                {
                    flag = true;
                }
            }
            catch
            {
                flag = true;
            }
            return flag;
        }

        public bool CheckGoodsArtno(string goodsArtno, int geId)
        {
            bool flag = false;
            try
            {
                string commandText = string.Format("select count(*) from tbGoodsEntitys where goodsArtno=@goodsArtno and id<>@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@goodsArtno", goodsArtno), new SqlParameter("@id", geId) };
                if (DbHelperSQL.ExecuteSql(commandText, commandParameters) > 0)
                {
                    flag = true;
                }
            }
            catch
            {
            }
            return flag;
        }

 

 


 

 



        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbGoodsEntitys";
            parameters[1].Value = "id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

